# -*- coding:utf-8 -*-
import xlrd
from users.models import CompanyInfo, AuthUser, Person, CompanyPerson
import re
import random
import string
from django.contrib.auth.hashers import make_password
from xlrd import xldate_as_tuple
from datetime import datetime
import json
from basic.models import ElevatorPersonInfo, ElevatorInfo
import hashlib
from maintenance.models import Contract
from ElevatorAdmin import settings
import csv


def data_import(dic, update_by):
    li = ['person', 'contract', 'elevator', 'task', 'wb_company', 'use_company']
    import_type = dic.keys()[0]
    import_file = dic[import_type]
    res = eval(import_type + '_import')(import_file, update_by)
    return res


def cell_parse(table, line, column):
    t = table.cell(line, column).ctype
    v = table.cell(line, column).value
    print 'line: %d, column: %d, value: %s, type: %s' % (line, column, v, t)
    ret = v.strip() if t else None
    return ret



def del_obj(qs):
    if qs:
        for obj in qs:
            obj.delete()


def get_valid_or_not(s):
    li = s.split('/')
    bo = False
    if len(li) == 3:
        t = '-'.join(li)
        try:
            datetime.strptime(t, "%Y-%m-%d")
            bo = True
        except Exception as e:
            print e
    return bo


def date_valid(s):
    if '-' in s:
        try:
            datetime.strptime(s, "%Y-%m-%d")
            return True
        except Exception:
            return False
    elif '/' in s:
        try:
            datetime.strptime(s, "%Y/%m/%d")
            return True
        except Exception:
            return False
    else:
        return False


def parse_ids(s):
    ret = []
    print s
    print type(s)
    if not s:
        return []
    if '\n' in s:
        arr = s.split('\n')
        for e in arr:
            ret.append(e.strip())
        return ret
    if '/' in s:
        arr = s.split('/')
        for e in arr:
            ret.append(e.strip())
        return ret
    elif ',' in s:
        arr = s.split(',')
        for e in arr:
            if e != '':
                ret.append(e.strip())
        return ret
    elif '，' in s:
        arr = s.split('，')
        for e in arr:
            if e != '':
                ret.append(e.strip())
        return ret
    # elif u',' in s:
    #     print s
    #     arr = s.split(u',')
    #     for e in arr:
    #         if e != '':
    #             ret.append(e.strip())
    #     return ret
    elif ' ' in s:
        arr = s.split(' ')
        for e in arr:
            if e != '':
                ret.append(e)
        return ret
    else:
        return [s]


def person_import(path, update_by):
    with open(path) as f:
        reader = csv.reader(f)
        rows = [row for row in reader]
        cols = ['home_addr', 't1_card_no', 't2_card_no', 't1_end_time',
                't2_end_time', 'contract_start_time', 'contract_end_time', 'real_name', 'card_id', 'phone', 'username']
        cols_req = [1, 0, 0, 0, 0, 1, 1, 1, 1, 1, 1]
        line = 1
        res = {}
        if len(rows) <= 1:
            res = {'line': 2, 'column': 1, 'msg': u'数据读取为空'}
            return res
        for row in rows[1:]:
            sanity = True
            line += 1
            row = [v.strip() for v in row]
            elements = list(enumerate(row))
            for element in elements:
                if not element[1] and cols_req[element[0]]:
                    res = {'line': line, 'column': element[0] + 1, 'msg': u'缺少必填项'}
                    sanity = False
                    print u'缺少必填项'
                    break
            if not sanity:
                break
            data = dict(zip(cols, row))

            t1_end_time = data.get('t1_end_time', '')
            if t1_end_time:
                if not date_valid(t1_end_time):
                    res = {'line': line, 'column': 4, 'msg': u'T1有效期处请输入字符串如2010/10/10'}
                    break

            t2_end_time = data.get('t2_end_time', '')
            if t2_end_time:
                if not date_valid(t2_end_time):
                    res = {'line': line, 'column': 5, 'msg': u'T2有效期处请输入字符串如2010/10/10'}
                    break

            contract_start_time = data.get('contract_start_time', '')
            if not contract_start_time:
                res = {'line': line, 'column': 6, 'msg': u'请输入合同开始时间'}
                break

            if not date_valid(contract_start_time):
                res = {'line': line, 'column': 6, 'msg': u'合同开始日期处请输入字符串如2010/10/10'}
                break

            contract_end_time = data.get('contract_end_time', '')
            if not contract_end_time:
                res = {'line': line, 'column': 7, 'msg': u'合同结束日期处请输入字符串如2010/10/10'}
                break

            if not date_valid(contract_end_time):
                res = {'line': line, 'column': 7, 'msg': u'合同结束日期处请输入字符串如2010/10/10'}
                break

            card_id = data.get('card_id', '')
            r = r'^([1-9]\d{5}[12]\d{3}(0[1-9]|1[012])(0[1-9]|[12][0-9]|3[01])\d{3}[0-9xX])$'
            card_pattern = re.compile(r)
            b = card_pattern.match(card_id)
            if not b:
                res = {'line': line, 'column': 8, 'msg': u'身份证号码错误'}
                break
            p = Person.objects.filter_active(card_id=card_id).first()
            if p:
                print {'line': line, 'column': 3, 'msg': u'人员重复'}
                continue

            phone = data.pop('phone', '')
            r = r'^(13\d|14[5|7]|15\d|166|17[3|6|7]|18\d)\d{8}$'
            phone_pattern = re.compile(r)
            b = phone_pattern.match(phone)
            if not b:
                res = {'line': line, 'column': 9, 'msg': u'手机号码格式错误'}
                break

            real_name = data.pop('real_name', '')

            username = data.pop('username', '')
            user = AuthUser.objects.filter(username=username).first()
            if user:
                res = {'line': line, 'column': 10, 'msg': u'维保人员账户重复，请重新填写'}
                break
            try:
                password = make_password('88888888')
                auth_user = AuthUser.objects.create(username=username, password=password, role=10, real_name=real_name,
                                                    phone=phone)
            except Exception as e:
                print e
                res = {'line': line, 'column': 10, 'msg': u'维保人员账户名错误，请重新填写'}
                break
            try:
                p = Person.objects.create(**data)
            except Exception as e:
                print e
                auth_user.delete()
                res = {'line': line, 'column': 10, 'msg': u'维保人员账户名错误，请重新填写'}
                break
            auth_obj = AuthUser.objects.get(id=update_by)
            company = auth_obj.company_person.company
            p.auth_user = auth_user
            p.company = company
            p.update_by = update_by
            p.service_id = company.id
            p.save()
        return res


def contract_import(path, update_by):

    with open(path) as f:
        print path
        reader = csv.reader(f)
        rows = [row for row in reader]
        cols = ['contract_name', 'contract_no', 'contract_type', 'maintain_cycle', 'start_time',
                'end_time', 'sign_time', 'property_company', 'elevator_ids']
        line = 1
        res = {}
        if len(rows) <= 1:
            res = {'line': 2, 'column': 1, 'msg': u'数据读取为空'}
            return res
        auth_obj = AuthUser.objects.get(id=update_by)
        company = auth_obj.company_person.company
        for row in rows[1:]:
            sanity = True
            line += 1
            row = [v.strip() for v in row[:9]]
            elements = list(enumerate(row))
            for element in elements:
                if not element[1]:
                    res = {'line': line, 'column': element[0] + 1, 'msg': u'缺少必填项'}
                    sanity = False
                    print u'缺少必填项'
                    break
            if not sanity:
                break
            data = dict(zip(cols, row))

            if data['contract_type'].decode('utf8') == u"质保":
                data['contract_type'] = 10
            elif data['contract_type'].decode('utf8') == u"保养":
                data['contract_type'] = 20
            elif data['contract_type'].decode('utf8') == u"维修":
                data['contract_type'] = 30
            else:
                res = {
                    "line": line, 'column': 3,
                    "msg": u"合同类型只能填写质保/保养/维修"
                }
                break

            if data['maintain_cycle'].decode('utf8') not in settings.MAINTAIN_CYCLE:
                res = {
                    "line": line, 'column': 4,
                    "msg": u"维保周期只能填写半月保/月保/季保/年保这四个选项！"
                }
                break
            data['maintain_cycle'] = settings.MAINTAIN_CYCLE[data['maintain_cycle'].decode('utf8')]

            if not date_valid(data['start_time']):
                res = {
                    "line": line, 'column': 5,
                    "msg": u"合同相关时间格式不对 eg: 2018-12-20 或者 2018/12/20"
                }
                break

            if not date_valid(data['end_time']):
                res = {
                    "line": line, 'column': 6,
                    "msg": u"合同相关时间格式不对 eg: 2018-12-20 或者 2018/12/20"
                }
                break

            if not date_valid(data['sign_time']):
                res = {
                    "line": line, 'column': 7,
                    "msg": u"合同相关时间格式不对 eg: 2018-12-20 或者 2018/12/20"
                }
                break

            property_company = CompanyInfo.objects. \
                filter_active(name=data['property_company'].decode('utf8'), company_type=300, service_id=company.id).first()
            print "service_id: %s" % company.id
            print data['property_company'].decode('utf8')
            if not property_company:
                res = {
                    "line": line, 'column': 8,
                    "msg": u"使用单位不存在 %s" % data['property_company'].decode('utf8')
                }
                break
            data['property_company'] = property_company
            data['maintain_company'] = company
            data['service_id'] = company.id
            data['update_by'] = update_by

            elevators = []
            sanity = True
            data['elevator_ids'] = parse_ids(data['elevator_ids'])
            for el in data['elevator_ids']:
                elevator = ElevatorInfo.objects.filter_active(register_code=el, service_id=company.id).first()
                if not elevator:
                    res = {
                        "line": line, 'column': 9,
                        "msg": u"电梯不存在 %s" % el
                    }
                    sanity = False
                    break
                elevators.append(elevator)
            if not sanity:
                break

            data.pop('elevator_ids')
            c = Contract.objects.filter_active(contract_no=data['contract_no'], service_id=company.id).first()
            if c is None:
                try:
                    c = Contract.objects.create(**data)
                except Exception, e:
                    res = {
                        "line": line, 'column': 9,
                        "msg": u"合同创建失败 %s 原因：%s" % (data['contract_no'], str(e))
                    }
                    break
            for el in elevators:
                el.contract = c
                el.save()

        return res


def elevator_import(path, update_by):

    with open(path) as f:
        reader = csv.reader(f)
        rows = [row for row in reader]
        cols = ['elevator_name', 'register_code', 'elevator_no', 'internal_no', 'factory_no', 'elevator_property',
                'rated_load', 'stations', 'elevator_type', 'usage_status', 'rated_speed', 'elevator_model',
                'product_company', 'installation_company', 'property_company_name',
                'wb_user', 'rescue_user', 'emergency_user']
        cols_req = [1, 1, 0, 0, 0, 1, 0, 0, 1, 1, 0, 0, 0, 0, 1, 0, 0, 0]
        line = 2
        res = {}
        if len(rows) <= 2:
            res = {'line': 3, 'column': 1, 'msg': u'数据读取为空'}
            return res
        for row in rows[2:]:
            sanity = True
            line += 1
            row = [v.strip() for v in row[:18]]
            elements = list(enumerate(row))
            print elements
            for element in elements:
                if not element[1] and cols_req[element[0]]:
                    res = {'line': line, 'column': cols_req[element[0]] + 1, 'msg': u'缺少必填项'}
                    sanity = False
                    print u'缺少必填项 : %s' % (cols_req[element[0]] + 1)
                    break
            if not sanity:
                break
            e = ElevatorInfo.objects.filter_active(register_code=row[1]).first()
            if e:
                print {'line': line, 'column': 2, 'msg': u'此注册代码已经注册过，不能重复注册'}
                continue
            data = dict(zip(cols, row))
            print data

            elevator_property = data.pop('elevator_property', '').decode('utf8')
            if elevator_property == u'安置房小区':
                elevator_property = 10
            elif elevator_property == u'商品房小区':
                elevator_property = 20
            elif elevator_property == u'医院、学校、机关事业单位':
                elevator_property = 30
            elif elevator_property == u'其他':
                elevator_property = 40
            else:
                res = {'line': line, 'column': 6, 'msg': u'请填写正确的设备性质，从上一单元格中选择其一'}
                break

            elevator_type = data.pop('elevator_type', '').decode('utf8')
            print elevator_type
            print type(elevator_type)
            if elevator_type == u"曳引与强制驱动电梯":
                elevator_type = 10
            elif elevator_type == u"液压驱动电梯":
                elevator_type = 20
            elif elevator_type == u"杂物电梯":
                elevator_type = 30
            elif elevator_type == u"自动扶梯":
                elevator_type = 40
            else:
                res = {'line': line, 'column': 9, 'msg': u'请填写正确的电梯类型，从上一单元格中选择其一'}
                break

            usage_status = data.pop('usage_status', '').decode('utf8')
            if usage_status == u"正常":
                usage_status = 10
            elif usage_status == u"正常,临时停用":
                usage_status = 20
            elif usage_status == u"正常,长期停用":
                usage_status = 30
            elif usage_status == u"正常,拆除":
                usage_status = 40
            elif usage_status == u"维修停用":
                usage_status = 50
            elif usage_status == u"已报停用":
                usage_status = 60
            elif usage_status == u"拆除":
                usage_status = 70
            else:
                res = {'line': line, 'column': 10, 'msg': u'请填写正确的使用状态，从上一单元格中选择其一'}
                break

            auth_user = AuthUser.objects.get(id=update_by)
            maintain_company = auth_user.company_person.company
            maintain_company_name = data.get('maintain_company_name', None)
            # if maintain_company.name != maintain_company_name:
            #     res = {'line': line, 'column': 15, 'msg': u'不能为其他维保公司添加添加电梯'}
            #     break
            property_company_name = data.get('property_company_name', None)
            property_company = CompanyInfo.objects.filter_active(name=property_company_name, company_type=300,
                                                                 service_id=maintain_company.id).first()
            if not property_company:
                res = {'line': line, 'column': 16, 'msg': u'未能在系统中找到此使用的单位，请核实名称 %s' % property_company_name.decode('utf8')}
                break

            wb_user = data.pop('wb_user')
            wb_user = parse_ids(wb_user)
            wb_list = []
            sanity = True
            for wu in wb_user:
                wb = AuthUser.objects.filter(username=wu).first()
                if not wb:
                    res = {'line': line, 'column': 17, 'msg': u'未能在系统中找到此帐号，请核实'}
                    print u"系统中无此人员"
                    sanity = False
                    break
                try:
                    if wb.person.service_id != maintain_company.id:
                        res = {'line': line, 'column': 17, 'msg': u'未能在系统中找到此维保人员，请核实'}
                        print u"service_i 不匹配"
                        sanity = False
                        break
                except Exception as e:
                    print e
                    res = {'line': line, 'column': 17, 'msg': u'未能在系统中找到此维保人员，请核实名称'}
                    sanity = False
                    break
                wb_list.append(wb)
            if not sanity:
                break

            rescue_user = data.pop('rescue_user', None)
            rescue_user = parse_ids(rescue_user)
            sanity = True
            rescue_list = []
            for ru in rescue_user:
                rescue = AuthUser.objects.filter(username=ru).first()
                if not rescue:
                    res = {'line': line, 'column': 18, 'msg': u'未能在系统中找到此帐号，请核实'}
                    sanity = False
                    break
                try:
                    if rescue.person.service_id != maintain_company.id:
                        res = {'line': line, 'column': 18, 'msg': u'未能在系统中找到此救援人员，请核实名称'}
                        sanity = False
                        break
                except Exception as e:
                    print e
                    res = {'line': line, 'column': 18, 'msg': u'未能在系统中找到此救援人员，请核实名称'}
                    sanity = False
                    break
                rescue_list.append(rescue)
            if not sanity:
                break

            emergency_user = data.pop('emergency_user', None)
            emergency_user = parse_ids(emergency_user)
            sanity = True
            emergency_list = []
            for eu in emergency_user:
                emergency = AuthUser.objects.filter(username=eu).first()
                if not emergency:
                    res = {'line': line, 'column': 19, 'msg': u'未能在系统中找到此帐号，请核实名称'}
                    sanity = False
                    break
                try:
                    if emergency.person.service_id != maintain_company.id:
                        res = {'line': line, 'column': 19, 'msg': u'未能在系统中找到此急救人员，请核实名称'}
                        sanity = False
                        break
                except Exception as e:
                    print e
                    res = {'line': line, 'column': 19, 'msg': u'未能在系统中找到此急救人员，请核实名称'}
                    sanity = False
                    break
                emergency_list.append(emergency)
            if not sanity:
                break

            data.update({
                'elevator_property': elevator_property,
                'elevator_type': elevator_type,
                'usage_status': usage_status,
                'maintain_company': maintain_company,
                'property_company': property_company,
                'service_id': maintain_company.id,
                'update_by': update_by,
            })
            ele = ElevatorInfo.objects.create(**data)
            ele_id = str(ele.id)
            md5 = hashlib.md5()
            md5.update(ele_id)
            qr_code = md5.hexdigest()
            ele.qr_code = qr_code
            ele.save()
            # obj_list.append(ele)

            for wb in wb_list:
                if not ElevatorPersonInfo.objects.filter(elevator=ele, worker=wb.person, worker_type=10).first():
                    ElevatorPersonInfo.objects.create(elevator=ele, worker=wb.person, worker_type=10)
            for rescue in rescue_list:
                if not ElevatorPersonInfo.objects.filter(elevator=ele, worker=rescue.person, worker_type=20).first():
                    ElevatorPersonInfo.objects.create(elevator=ele, worker=rescue.person, worker_type=20)
            for emergency in emergency_list:
                if not ElevatorPersonInfo.objects.filter(elevator=ele, worker=rescue.person, worker_type=30).first():
                        ElevatorPersonInfo.objects.create(elevator=ele, worker=emergency.person, worker_type=30)

    return res


def wb_company_import(path, update_by):
    return ''


def use_company_import(path, update_by):

    with open(path) as f:
        reader = csv.reader(f)
        rows = [row for row in reader]
        cols = ['name', 'username', 'unified_code', 'legal_person', 'legal_phone', 'person_in_charge',
                'person_in_charge_phone', 'company_telephone']
        cols_req = [1, 1, 0, 0, 0, 0, 0, 0]
        line = 1
        res = {}
        if len(rows) <= 1:
            res = {'line': 2, 'column': 1, 'msg': u'数据读取为空'}
            return res
        # print rows
        for row in rows[1:]:
            sanity = True
            line += 1
            row = [v.strip() for v in row[:8]]
            elements = list(enumerate(row))
            print elements
            for element in elements:
                print element
                print cols_req
                if not element[1] and cols_req[element[0]]:
                    res = {'line': line, 'column': element[0] + 1, 'msg': u'缺少必填项'}
                    sanity = False
                    print u'缺少必填项'
                    break
            if not sanity:
                break
            auth_obj = AuthUser.objects.get(id=update_by)
            maintain_company_obj = CompanyPerson.objects.get(auth_user=auth_obj.id).company
            com = CompanyInfo.objects.filter_active(name=row[0], service_id=maintain_company_obj.id).first()
            if com:
                res = {'line': line, 'column': 1, 'msg': u'使用单位重复'}
                print u'单位重复'
                continue
            user = AuthUser.objects.filter(username=row[1]).first()
            print "row[1]", row[1]
            if user:
                sanity = False
                print u'人员重复'
                break
            data = dict(zip(cols, row))
            username = data.pop('username', None)
            use_company_obj = CompanyInfo.objects.create(**data)
            password = make_password('88888888')
            try:
                auth_user = AuthUser.objects.create(username=username, password=password, role=30)
            except Exception, e:
                print e
                use_company_obj.delete()
                res = {'line': line, 'column': 1, 'msg': u'使用单位重复'}
                break
            use_company_obj.update_by = update_by
            use_company_obj.service_id = maintain_company_obj.id
            use_company_obj.company_type = 300
            use_company_obj.save()
            CompanyPerson.objects.create(company=use_company_obj, auth_user=auth_user, person_type=20)
            print "line: %s" % line
        return res


